package com.fahai.web.controller;

import com.fahai.common.PageVo;
import com.fahai.utils.CommonUtil;
import com.fahai.utils.DateUtil;
import com.fahai.utils.json.JsonResult;
import com.fahai.web.entity.DataAnalyse;
import com.fahai.web.service.DataAnalysisService;
import com.fahai.web.vo.DataAnalyseQueryVO;
import org.apache.poi.hssf.usermodel.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.security.config.annotation.method.configuration.EnableGlobalMethodSecurity;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.ResponseBody;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.List;

/**
 * 数据分析
 */

@Controller
@RequestMapping("/data")
@EnableGlobalMethodSecurity(prePostEnabled = true)
public class DataAnalyseController extends BaseController {
    private static Logger logger = LoggerFactory.getLogger(DataAnalyseController.class);

    @Autowired
    DataAnalysisService service;

    /**
     * 返回界面
     *
     * @param model
     * @return
     */
    @RequestMapping(value = "/list", method = RequestMethod.GET)
    public String goPage(Model model) {

        model.addAttribute("selectTab", "数据分析");

        return "data_analyse";
    }

    /**
     * 查询列表
     *
     * @param pageVo
     * @return
     */
    @ResponseBody
    @RequestMapping(value = "/querydataAnalyseList", method = RequestMethod.POST)
    public JsonResult query(PageVo pageVo, Model model, DataAnalyseQueryVO vo) {
        PageVo pageVoData = service.findDataList(vo, pageVo);
        model.addAttribute("selectTab", "数据分析");
        return ok("获取充值产品设定数据", pageVoData);
    }

    /**
     * 查询汇总
     *
     * @param pageVo
     * @return
     */
    @ResponseBody
    @RequestMapping(value = "/sum", method = RequestMethod.POST)
    public JsonResult sum(PageVo pageVo, Model model, DataAnalyseQueryVO vo) {
        PageVo pageVoData = service.findDataSum(vo, pageVo);
        model.addAttribute("selectTab", "数据分析");
        return ok("获取充值产品设定数据", pageVoData);
    }

    /**
     * 页码总数量
     *
     * @param pageVo
     * @return
     */
    @ResponseBody
    @RequestMapping(value = "/count", method = RequestMethod.POST)
    public JsonResult count(PageVo pageVo, Model model, DataAnalyseQueryVO vo) {
        int i = service.count(vo);
        model.addAttribute("selectTab", "数据分析");
        return ok("获取充值产品设定数据", i);
    }

    /**
     * 总数行 数据查询
     *
     * @param pageVo
     * @return
     */
    @ResponseBody
    @RequestMapping(value = "/countrow", method = RequestMethod.POST)
    public JsonResult countrow(PageVo pageVo, Model model, DataAnalyseQueryVO vo) {
        PageVo pageVoData = service.findDataList(vo, pageVo);
        model.addAttribute("selectTab", "数据分析");
        return ok("获取充值产品设定数据", pageVoData);
    }

    /**
     * 导出xls
     * @param response
     * @param request
     */
    @ResponseBody
    @RequestMapping(value = "/exportExcel")
    public void exportExcel(  HttpServletResponse response, HttpServletRequest request) {
        String[] headers = new String[]{"客户ID", "客户姓名/昵称", "客户手机号", "客户手机号归属地", "当前捆绑律师", "首次咨询时间",
                "末次咨询时间", "客户创建时间", "通话时长", "通话结果", "咨询有效性", "咨询发起方", "咨询发起时刻",
                "服务律师ID", "服务律师姓名", "服务律师电话", "服务律师状态", "服务律师地区"};
        DataAnalyseQueryVO vo = new DataAnalyseQueryVO();
        vo.setUname(request.getParameter("uname"));//客户名称
        vo.setUphone(request.getParameter("uphone"));//客户电话
        vo.setUphoneArea(request.getParameter("uphoneArea"));//客户手机号归属地
        vo.setLawyername(request.getParameter("lawyername"));//捆绑律师名
        vo.setAlawyername(request.getParameter("alawyername"));//电话咨询律师名
        vo.setFirstTimeStart(request.getParameter("firstTimeStart"));//首次咨询
        vo.setLastTimeStart(request.getParameter("lastTimeStart"));//末次咨询
        vo.setLastTimeEnd(request.getParameter("lastTimeEnd"));//末次咨询
        vo.setCreateTimeStart(request.getParameter("createTimeStart"));
        vo.setCreateTimeEnd(request.getParameter("createTimeEnd"));
        vo.setLawyerPhone(request.getParameter("lawyerPhone"));
        if(null!=request.getParameter("lawyerStates")&&!"".equals(request.getParameter("lawyerStates"))){
            vo.setLawyerStates(Integer.valueOf(request.getParameter("lawyerStates")));
        }
        if(null!=request.getParameter("initiator")&&!"".equals(request.getParameter("initiator"))){
            vo.setInitiator(Integer.valueOf(request.getParameter("initiator")));
        }
        if(null!=request.getParameter("callStatus")&&!"".equals(request.getParameter("callStatus"))){
            vo.setCallStatus(Integer.valueOf(request.getParameter("callStatus")));
        }

        vo.setStartTimeStart(request.getParameter("startTimeStart"));
        vo.setStartTimeEnd(request.getParameter("startTimeEnd"));
        vo.setCalleffect(request.getParameter("calleffect"));
       vo.setXlsgroup(request.getParameter("xlsgroup"));
        vo.setAcid(request.getParameter("acid"));
        PageVo pageVo= new  PageVo();
        PageVo pageVoData = service.findDataListXls(vo, pageVo);
        //List<DataAnalyse> vos = (List<DataAnalyse>) pageVoData.getResult();
        try {
             expExcel(request, response, vo);
        } catch (IOException e) {
            e.printStackTrace();
        }

    }







    /**
     *导出xls
     * @param request
     * @param resp
     * @param  dataAnalyseQueryVO 查询条件
     * @return
     * @throws IOException
     */
    public   void expExcel(HttpServletRequest request, HttpServletResponse resp, DataAnalyseQueryVO dataAnalyseQueryVO) throws IOException {



        PageVo pageVo= new PageVo();
        List<DataAnalyse> vos_call =service.getCallList(dataAnalyseQueryVO);
        List<DataAnalyse> vos_lawyer=service.getLawyerList(dataAnalyseQueryVO);;
        List<DataAnalyse> vos_client=service.getClientList(dataAnalyseQueryVO);;


        HSSFWorkbook wb = new HSSFWorkbook();
        request.setCharacterEncoding("UTF-8");
        resp.setCharacterEncoding("UTF-8");
        resp.setContentType("application/x-download");
        String fileName = "数据分析"+System.currentTimeMillis()+".xls";
        fileName = URLEncoder.encode(fileName, "UTF-8");
        resp.addHeader("Content-Disposition", "attachment;filename=" + fileName);
        String[] headers={"用户Id","用户姓名","用户电话","用户注册时间","用户归属地","用户余额","律师Id","律师姓名","律师电话","律师余额","律师服务地","律师来源","雇佣类型","律师状态","咨询发起时间","首次咨询时间","通话时长","通话结果","咨询有效性","发起方","咨询发起位置"};
        HSSFSheet sheet = wb.createSheet("数据分析");
        sheet.setDefaultRowHeight((short) (2 * 256));
        //sheet.setColumnWidth(0, 50 * 160);
        HSSFCellStyle cellStyle = wb.createCellStyle();
        HSSFCellStyle cellStyle_content = wb.createCellStyle();

        HSSFRow row =sheet.createRow(0);
        HSSFFont headerFont = wb.createFont(); // 创建字体样式
        headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 字体加粗
        headerFont.setFontName("宋体"); // 设置字体类型
        //headerFont.setColor(HSSFFont.COLOR_RED);
        headerFont.setFontHeightInPoints((short) 14); // 设置字体大小
        cellStyle.setFont(headerFont); // 为标题样式设置字体样式
        cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
        cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
        cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
        cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中


        for (int i=0;i<headers.length;i++) {
            HSSFCell cell = row.createCell(i);
            cell.setCellValue(headers[i]);
            cell.setCellStyle(cellStyle);
        }

        HSSFFont font = wb.createFont();
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 14);
        int rowCount=0;
        if(null!=vos_call&&vos_call.size()>0){
            for (int i = 0; i < vos_call.size(); i++)
            {
                HSSFRow row1 = sheet.createRow((int) i + 1);
                rowCount++;
                //row1.setRowStyle(cellStyle);
                DataAnalyse vo = vos_call.get(i);
                int j=0;
                //row1.createCell(j++).setCellValue(i + 1);
                HSSFCell cell;
                cellStyle_content.setAlignment(HSSFCellStyle.ALIGN_RIGHT);// 水平方向的对齐方式
                row1.createCell(j++).setCellValue(String.valueOf(vo.getUserid()==null?"":vo.getUserid()));//客户ID
                row1.createCell(j++).setCellValue(String.valueOf(vo.getUname()==null?"":vo.getUname()));//客户姓名/昵称
                row1.createCell(j++).setCellValue(String.valueOf(vo.getUphone()==null?"":vo.getUphone()));//用户手机号
                row1.createCell(j++).setCellValue(String.valueOf(vo.getCreateTime()==null?"":DateUtil.TimeStampToString(vo.getCreateTime(), "yyyy-MM-dd HH:mm:ss") ));//用户注册时间
                row1.createCell(j++).setCellValue(String.valueOf(vo.getUphoneArea()==null?"":vo.getUphoneArea()));//用户归属地
                //用户余额
                cell= row1.createCell(j++);
                cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                cell.setCellStyle(cellStyle_content);
                cell.setCellValue(String.valueOf(vo.getUrmoney()==null?"":vo.getUrmoney()));
                row1.createCell(j++).setCellValue(String.valueOf(vo.getAlawyerid()==null?"":vo.getAlawyerid() ));//服务律师ID
                row1.createCell(j++).setCellValue(String.valueOf(vo.getAlawyername()==null?"":vo.getAlawyername() ));//服务律师名称
                row1.createCell(j++).setCellValue(String.valueOf(vo.getAlawyerPhone()==null?"":vo.getAlawyerPhone() ));//服务律师电话
                row1.createCell(j++).setCellValue(String.valueOf(vo.getAllDistillMoney()==null?"":vo.getAllDistillMoney()));//服务律师余额
                row1.createCell(j++).setCellValue(String.valueOf(vo.getAcname() ==null?"":vo.getAcname()));//服务地区
                row1.createCell(j++).setCellValue("");//TODO 律师来源
                row1.createCell(j++).setCellValue("");//TODO 雇佣类型
                row1.createCell(j++).setCellValue(String.valueOf(vo.getLawyerStatesStr() ==null?"":vo.getLawyerStatesStr()));//律师状态
                row1.createCell(j++).setCellValue(String.valueOf(vo.getStartTimeStr()==null?"":vo.getStartTimeStr()) );//开始时间
                row1.createCell(j++).setCellValue(String.valueOf(vo.getFirstTime()==null?"":DateUtil.TimeStampToString(vo.getFirstTime(), "yyyy-MM-dd HH:mm:ss") ));//第一次咨询时间
                //通话时长
                cell=row1.createCell(j++);
                cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                cell.setCellStyle(cellStyle_content);
                cell.setCellValue(String.valueOf(vo.getCalledDuration()==null?"":vo.getCalledDuration())+"秒");
                row1.createCell(j++).setCellValue(CommonUtil.parseThjg(vo.getCallStatus()==null?0:vo.getCallStatus(),vo.getRejectStatus()==null?0:vo.getRejectStatus()));//通话结果
                row1.createCell(j++).setCellValue(String.valueOf(vo.getIsEffectiveStr()==null?"":vo.getIsEffectiveStr()) );//是否有效
                row1.createCell(j++).setCellValue(String.valueOf(vo.getInitiatorStr()==null?"":vo.getInitiatorStr() ));//发起方

                row1.createCell(j++).setCellValue(String.valueOf(vo.getProid() ==null?"":vo.getProid()==1?"法海问法":"手机M站"));//TODO 咨询发起位置
            }
        }



        if(null!=vos_lawyer&&vos_lawyer.size()>0){
            for (int i = 0; i < vos_lawyer.size(); i++)
            {
                HSSFRow row1 = sheet.createRow((int) rowCount + 1);
                rowCount++;
                //row1.setRowStyle(cellStyle);
                DataAnalyse vo = vos_lawyer.get(i);
                int j=0;
                row1.createCell(j++).setCellValue("");//客户ID
                row1.createCell(j++).setCellValue("");//客户姓名/昵称
                row1.createCell(j++).setCellValue("");//用户手机号
                row1.createCell(j++).setCellValue("");//用户注册时间
                row1.createCell(j++).setCellValue("");//用户归属地
                row1.createCell(j++).setCellValue("");//用户余额
                row1.createCell(j++).setCellValue(String.valueOf(vo.getAlawyerid()==null?"":vo.getAlawyerid() ));//服务律师ID
                row1.createCell(j++).setCellValue(String.valueOf(vo.getAlawyername()==null?"":vo.getAlawyername() ));//服务律师名称
                row1.createCell(j++).setCellValue(String.valueOf(vo.getAlawyerPhone()==null?"":vo.getAlawyerPhone() ));//服务律师电话
                row1.createCell(j++).setCellValue(String.valueOf(vo.getAllDistillMoney()==null?"":vo.getAllDistillMoney()));//服务律师余额
                row1.createCell(j++).setCellValue(String.valueOf(vo.getAcname() ==null?"":vo.getAcname()));//服务地区
                row1.createCell(j++).setCellValue("");//TODO 律师来源
                row1.createCell(j++).setCellValue("");//TODO 雇佣类型
                row1.createCell(j++).setCellValue(String.valueOf(vo.getLawyerStatesStr() ==null?"":vo.getLawyerStatesStr()));//律师状态
                row1.createCell(j++).setCellValue("");//开始时间

                row1.createCell(j++).setCellValue("");//第一次咨询时间

                row1.createCell(j++).setCellValue("");//通话时长
                row1.createCell(j++).setCellValue("");//通话结果
                row1.createCell(j++).setCellValue("");//是否有效
                row1.createCell(j++).setCellValue("");//发起方

                row1.createCell(j++).setCellValue("");//TODO 咨询发起位置
            }
        }



        if(null!=vos_client&&vos_client.size()>0){

            HSSFFont font_client=wb.createFont();
         //   font_client.setColor(HSSFColor.GREEN.index);//HSSFColor.VIOLET.index //字体颜色

            for (int i = 0; i < vos_client.size(); i++)
            {
                HSSFRow row1 = sheet.createRow((int) rowCount + 1);
                rowCount++;
                //row1.setRowStyle(cellStyle);
                DataAnalyse vo = vos_client.get(i);
                int j=0;
                HSSFCell cell;
                cellStyle_content.setAlignment(HSSFCellStyle.ALIGN_RIGHT);// 水平方向的对齐方式
                cellStyle_content.setFont(font_client);
                //row1.createCell(j++).setCellValue(i + 1);
                row1.createCell(j++).setCellValue(String.valueOf(vo.getUserid()==null?"":vo.getUserid()));//客户ID
                row1.createCell(j++).setCellValue(String.valueOf(vo.getUname()==null?"":vo.getUname()));//客户姓名/昵称
                row1.createCell(j++).setCellValue(String.valueOf(vo.getUphone()==null?"":vo.getUphone()));//用户手机号
                row1.createCell(j++).setCellValue(String.valueOf(vo.getCreateTime()==null?"": DateUtil.TimeStampToString(vo.getCreateTime(), "yyyy-MM-dd HH:mm:ss") ));//用户注册时间
                row1.createCell(j++).setCellValue(String.valueOf(vo.getUphoneArea()==null?"":vo.getUphoneArea()));//用户归属地
                //用户余额
                cell= row1.createCell(j++);
                cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                cell.setCellStyle(cellStyle_content);
                cell.setCellValue(String.valueOf(vo.getUrmoney()==null?"":vo.getUrmoney()));

                row1.createCell(j++).setCellValue("");//服务律师ID
                row1.createCell(j++).setCellValue("");//服务律师名称
                row1.createCell(j++).setCellValue("");//服务律师电话
                row1.createCell(j++).setCellValue("");//服务律师余额
                row1.createCell(j++).setCellValue("");//服务地区
                row1.createCell(j++).setCellValue("");//TODO 律师来源
                row1.createCell(j++).setCellValue("");//TODO 雇佣类型
                row1.createCell(j++).setCellValue("");//律师状态
                row1.createCell(j++).setCellValue("");//开始时间

                row1.createCell(j++).setCellValue("");//第一次咨询时间

                row1.createCell(j++).setCellValue("");//通话时长
                row1.createCell(j++).setCellValue("");//通话结果
                row1.createCell(j++).setCellValue("" );//是否有效
                row1.createCell(j++).setCellValue("");//发起方

                row1.createCell(j++).setCellValue("");//TODO 咨询发起位置
            }
        }


        OutputStream out = resp.getOutputStream();
        wb.write(out);
        out.close();
    }

}
